# 人生苦短，我用python
# @Time : 2021/10/11 10:17
# @Author : shiChao
# @File : excel.py
# @Desc : excel读写,写的方法只针对项目里的Excel格式

import xlrd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment
from openpyxl.styles.colors import COLOR_INDEX


class Excel:

    # Excel数据
    data = {}
    wb = {}
    ws = {}
    table = ''

    # 初始化
    def __init__(self, fileName: str):
        self.fileName = fileName

    """
        读
        :param fileName:文件
        :param SheetName: 单元格名称
    """
    def readExcel(self, sheetName="Sheet1"):
        self.data = xlrd.open_workbook(self.fileName)
        self.table = self.data.sheet_by_name(sheetName)

        # 获取总行数、总列数
        allRows = self.table.nrows

        if allRows > 1:
            # 获取第一行的内容，列表格式
            keys = self.table.row_values(0)
            listApiData = []
            # 获取每一行的内容，列表格式
            for col in range(1, allRows):
                values = self.table.row_values(col)
                # keys，values组合转换为字典
                api_dict = dict(zip(keys, values))
                listApiData.append(api_dict)
            return listApiData
        else:
            print("表格是空数据!")
            return None

    """
        写
        :param fileName:文件
        :param row_n:数据所在行数
        :param value: 测试结果值
    """
    def writeExcel(self, row_n: int, value: str):
        # 打开文件
        self.wb = load_workbook(self.fileName)
        self.ws = self.wb.active

        fontGREEN = Font(name='宋体', color=COLOR_INDEX[3], bold=True)
        fontRED = Font(name='宋体', color=COLOR_INDEX[2], bold=True)
        font1 = Font(name='宋体', color=COLOR_INDEX[19], bold=True)
        align = Alignment(horizontal='center', vertical='center')

        # 获数所在行数
        L_n = "L" + str(row_n)
        M_n = "M" + str(row_n)

        if value == "PASS":
            self.ws.cell(row_n, 12, value)
            self.ws[L_n].font = fontGREEN

        if value == "FAIL":
            self.ws.cell(row_n, 12, value)
            self.ws[L_n].font = fontRED

        # 【选填】 针对Excel，写入用例执行人，参数可以写入配置
        # self.ws.cell(row_n, 13, 'TESTER')
        self.ws[L_n].alignment = align
        self.ws[M_n].font = font1
        self.ws[M_n].alignment = align
        self.wb.save(self.fileName)


